package o2o.web.actions.statis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jinyou.utils.common.*;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import o2o.cache.shop.ShopMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.config.annotation.LoginUser;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.common.ResultInfo;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.dao.PO.order.OrderStatementStatistics;
import o2o.company.model.ShopInfo;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.VO.order.OrderStatementStatisticsVO;
import o2o.dao.VO.order.REFUND_APPLY_STATUS;
import o2o.dao.VO.statis.ShopTradeInfoVo;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.task.order.OrderBakTaskUtils;
import o2o.task.order.OrderStatisticsUtils;
import o2o.utils.CommonUtils;
import o2o.web.actions.company.SysCityCompanyMan;
import o2o.web.actions.user.User;
import o2o.web.actions.wallet.UserWalletMan;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import pay.ChannelUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description: TODO
 * Created with IntelliJ IDEA.
 * User: Jahnke [1029777564@qq.com]
 * Date: 2019-02-20 10:50
 * To change this template use File | Settings | File Templates.
 */
@Controller
public class ShopTradeStatisController {

    // 获取店铺的营收信息
    @RequestMapping("admin/money/shop/trade/statistics")
    @ResponseBody
    public String shopTradeStatis(String token, String province, String city, String county,
                                  @RequestParam(defaultValue = "0") Integer isNotShop, Long agentId,
                                  String username, Long shopId, Long startTime, Long endTime, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代和店铺自己可查看
//        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.AGENT_MANAGER && user.getUserType() != UserType.SUPER_COMPANY && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER
                && user.getUserType() != UserType.BUSINESS_MAN
        ) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }

        if (user.getUserType() == UserType.COMPANY) {
            user.setUserType(UserType.ADMIN);
            String t = SysCityCompanyMan.getSysCompanyOwnUsername(user.getUsername());
            if (StringUtils.isEmpty(t)) {
                return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
            }
            user.setUsername(t);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        /*
            SELECT SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalprice,0) ELSE 0 END) allPayMoney,
                SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalmoney,0) ELSE 0 END) allPayPrice,
                SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN 1 ELSE 0 END) orderCounts,
                COUNT(ori.orderNo) orderCounts2,
                SUM(CASE WHEN ori.isRefundApply=2 THEN 1 ELSE 0 END) refundApplyCount,
                SUM(CASE WHEN ori.isRefundApply=2  THEN ori.totalprice ELSE 0 END) refundApplyMoney,
                SUM(IFNULL(orp.cityAdminMoney,0)) AS cityAgentMoney,
                SUM(IFNULL(orp.shopAwardMoney,0)) AS shopAwardMoney,
                SUM(IFNULL(orp.platformAwardMoney,0)) AS platformAwardMoney,
                SUM(IFNULL(orp.shopMoney,0)) AS shopMoney,
                SUM(IFNULL(orp.postmanMoney,0)) AS postmanMoney
            FROM order_info AS ori
            LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno
            LEFT JOIN shop_info AS sfo ON sfo.id=ori.shopId
            LEFT JOIN shop_agent_relation AS sar ON sar.shopId = ori.shopId
            WHERE sfo.delFlag=0
         */

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }
        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        // 检查骑手是否账号重复，重复的话，删除重复的
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  id,username  FROM ").append(PojoTables.CityPostman).append(" a ")
                .append(" WHERE delflag =0 AND username IN (SELECT username FROM city_postman GROUP BY username HAVING COUNT(username) > 1)")
                .append(" ORDER BY username ");
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sb.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql = "SELECT SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalMoney,0) ELSE 0 END) allPayMoney,  " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalprice,0) ELSE 0 END) allPayPrice, " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformMoney,0) ELSE 0 END) AS platformMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.shopMoney,0) ELSE 0 END) AS shopMoney," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalGoodsMoney,0) ELSE 0 END) AS totalGoodsMoney," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) AS postmanMoney," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.squareMoney,0) ELSE 0 END) squareMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postCompanyMoney,0) ELSE 0 END) AS postCompanyMoney," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND cp.type = 6 THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) AS postmanMoneyTwo," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.originalYunFeiShop,0) ELSE 0 END) AS originalYunFeiShop," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.originalYunFei,0) ELSE 0 END) AS originalYunFei," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND cp.type = 6 THEN IFNULL(ori.originalYunFei,0) ELSE 0 END) AS originalYunFeiTwo," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.deliveryPrice,0) ELSE 0 END) AS deliveryPrice," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.packetPrice,0) ELSE 0 END) AS packetPrice," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.secondaryPostmanMoney,0) ELSE 0 END) AS secondaryPostmanMoney," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.businessMoney,0) ELSE 0 END) AS businessMoney,       " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformAwardMoney,0) ELSE 0 END) AS platformAwardMoney,  " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.shopAwardmoney,0) ELSE 0 END) AS shopAwardmoney,  " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN 1 ELSE 0 END) orderCounts,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND orp.isOrderCancel=0 THEN IFNULL(orp.agentManagerMoney,0) ELSE 0 END) agentManagerMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND (ori.payType='" + ChannelUtils.wx + "' or ori.payType='" + ChannelUtils.wx_xcx + "' or ori.payType='" + ChannelUtils.wx_gzh + "') THEN IFNULL(ori.totalprice,0) ELSE 0 END) wxPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND ori.payType='" + ChannelUtils.alipay + "' THEN IFNULL(ori.totalprice,0) ELSE 0 END) zfbPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND ori.payType='" + ChannelUtils.transit + "' THEN IFNULL(ori.totalprice,0) ELSE 0 END) transitPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND ori.payType='wallet' THEN IFNULL(ori.totalprice,0) ELSE 0 END) walletPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND ori.payType='" + ChannelUtils.paypal + "' THEN IFNULL(ori.totalprice,0) ELSE 0 END) paypalPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND ori.payType='" + ChannelUtils.stripe + "' THEN IFNULL(ori.totalprice,0) ELSE 0 END) stripePrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND ori.payType!='" + ChannelUtils.paypal + "'" +
                " AND ori.payType!='" + ChannelUtils.wx + "'" + " AND ori.payType!='" + ChannelUtils.wx_xcx + "'" + " AND ori.payType!='" + ChannelUtils.wx_gzh + "'" + " AND ori.payType!='" + ChannelUtils.alipay + "'" + " AND ori.payType!='" + ChannelUtils.transit + "'" + " AND ori.payType!='" + ChannelUtils.stripe + "'" +
                " AND ori.payType!='" + ChannelUtils.transit + "'" + " AND ori.payType!='wallet'" +
                " THEN IFNULL(ori.totalprice,0) ELSE 0 END) othPrice,        " +
                "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount," +
                "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney1,0) ELSE 0 END) cityAdminMoney1," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney2,0) ELSE 0 END) cityAdminMoney2," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney3,0) ELSE 0 END) cityAdminMoney3," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney4,0) ELSE 0 END) cityAdminMoney4," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward1,0) ELSE 0 END) cityAdminAward1," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward2,0) ELSE 0 END) cityAdminAward2," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward3,0) ELSE 0 END) cityAdminAward3," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward4,0) ELSE 0 END) cityAdminAward4," +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney,0) ELSE 0 END) AS cityAgentMoney,    " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward,0) ELSE 0 END) AS cityAdminAward,    " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.disCommisionMoney,0) ELSE 0 END) AS disCommisionMoney,    " +
                "COUNT(DISTINCT CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN ori.username ELSE NULL END) userCount,       " +
                "FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS oriDate  " +
                "FROM " + t_order_info + " AS ori " +
                "LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno and orp.delFlag = 0  " +
                "LEFT JOIN " + PojoTables.ShopInfo + " AS si ON si.id=ori.shopId and si.delFlag = 0 " +
                "LEFT JOIN " + PojoTables.CityPostman + " AS cp ON cp.username=ori.postmanUsername AND cp.delFlag = 0  " +
                (StringUtils.isNotEmpty(username) ? ("LEFT JOIN user_agent_relation AS uar ON ori.agentId=uar.agentId  ") : "") +
                ((user.getUserType() == UserType.AGENT_MANAGER) ?
                        " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=ori.agentId and cag.delFlag =0  " : " "
                ) +
                " WHERE  ori.delFlag=0 and ori.orderNo not like 'S%' " +
                (isNotShop == 1 ? "" : (" and ori.shopId > 0 ")) +
                (StringUtils.isNotEmpty(username) ? (" and uar.delFlag=0 ") : "") +
                (shopId != null && shopId > 0 ? (" and ori.shopId=" + shopId) : "") +
                (agentId != null ? (" and ori.agentId=" + agentId) : "") +
                (StringUtils.isNotEmpty(city) ? (" AND ori.shopCity='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND si.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(username) ? (" AND uar.username='" + username + "'") : "") +
                (user.getUserType() == UserType.AGENT_MANAGER ? (" AND cag.agentManagerUsername='" + user.getUsername() + "'") : "") +
                " AND ori.createtime BETWEEN " + startTime + " AND " + endTime +
                " GROUP BY oriDate desc";
        List<ShopTradeInfoVo> lists = null;
        try {
//            System.out.println("财务概览：\n" + sql);
            lists = (List<ShopTradeInfoVo>) UserDaoSql.getInstance().list(ShopTradeInfoVo.class, sql);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        ShopTradeInfoVo info = null;
        if (lists != null && !lists.isEmpty()) {
            info = new ShopTradeInfoVo();
            for (int i = 0; i < lists.size(); i++) {
                ShopTradeInfoVo a = lists.get(i);
                info.setAllPayMoney(a.getAllPayMoney() + info.getAllPayMoney());
                info.setAllPayPrice(a.getAllPayPrice() + info.getAllPayPrice());
                info.setOrderCounts(a.getOrderCounts() + info.getOrderCounts());
                info.setOrderCounts2(a.getOrderCounts2() + info.getOrderCounts2());
                info.setRefundApplyCount(a.getRefundApplyCount() + info.getRefundApplyCount());
                info.setRefundApplyMoney(a.getRefundApplyMoney() + info.getRefundApplyMoney());
                info.setCityAgentMoney(a.getCityAgentMoney() + info.getCityAgentMoney());
                info.setCityAdminAward(a.getCityAdminAward() + info.getCityAdminAward());
                info.setShopAwardMoney(a.getShopAwardMoney() + info.getShopAwardMoney());
                info.setShopMoney(a.getShopMoney() + info.getShopMoney());
                info.setSquareMoney(a.getSquareMoney() + info.getSquareMoney());
                info.setPostCompanyMoney(a.getPostCompanyMoney() + info.getPostCompanyMoney());
                info.setPlatformAwardMoney(a.getPlatformAwardMoney() + info.getPlatformAwardMoney());
                info.setPlatformMoney(a.getPlatformMoney() + info.getPlatformMoney());
                info.setPostmanMoney(a.getPostmanMoney() + info.getPostmanMoney());
                info.setDeliveryPrice(a.getDeliveryPrice() + info.getDeliveryPrice());
                info.setOriginalYunFei(a.getOriginalYunFei() + info.getOriginalYunFei());
                info.setOriginalYunFeiShop(a.getOriginalYunFeiShop() + info.getOriginalYunFeiShop());
                info.setTotalGoodsMoney(a.getTotalGoodsMoney() + info.getTotalGoodsMoney());
                info.setPacketPrice(a.getPacketPrice() + info.getPacketPrice());
                info.setUserCount(a.getUserCount() + info.getUserCount());
                info.setAgentManagerMoney(a.getAgentManagerMoney() + info.getAgentManagerMoney());
                info.setCityAdminMoney1(a.getCityAdminMoney1() + info.getCityAdminMoney1());
                info.setCityAdminMoney2(a.getCityAdminMoney2() + info.getCityAdminMoney2());
                info.setCityAdminMoney3(a.getCityAdminMoney3() + info.getCityAdminMoney3());
                info.setCityAdminMoney4(a.getCityAdminMoney4() + info.getCityAdminMoney4());
                info.setCityAdminAward1(a.getCityAdminAward1() + info.getCityAdminAward1());
                info.setCityAdminAward2(a.getCityAdminAward2() + info.getCityAdminAward2());
                info.setCityAdminAward3(a.getCityAdminAward3() + info.getCityAdminAward3());
                info.setCityAdminAward4(a.getCityAdminAward4() + info.getCityAdminAward4());
                info.setDisCommisionMoney(a.getDisCommisionMoney() + info.getDisCommisionMoney());
            }
        }
        int totalCount = lists == null ? 0 : lists.size();
        // 分页
        lists = (List<ShopTradeInfoVo>) ToolUtils.list(lists, (page - 1) * size + 1, page * size);

        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("totalCount", totalCount);
        obj.put("data", JSONArray.toJSON(lists == null ? Collections.emptyList() : lists));
        obj.put("info", JSONObject.toJSON(info));
        return obj.toString();
    }

    // 跑腿统计
    @RequestMapping("admin/money/paotui/trade/statistics")
    @ResponseBody
    public String paotuiTradeStatis(String token, String province, String city, String county, String username, Long startTime, Long endTime, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代和店铺自己可查看
//        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_COMPANY && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }

        if (user.getUserType() == UserType.COMPANY) {
            user.setUserType(UserType.ADMIN);
            String t = SysCityCompanyMan.getSysCompanyOwnUsername(user.getUsername());
            if (StringUtils.isEmpty(t)) {
                return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
            }
            user.setUsername(t);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
//            endTime = new Date().getTime();
            endTime = DateUtils.getCurrTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 20;
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String dataDbName = OrderBakTaskUtils.getDataDbName();
        // 获取统计里的数据
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT DATE_FORMAT(oriDate, '%Y-%m-%d') oriDate,IFNULL(SUM(orderCounts),0) orderCounts, IFNULL(SUM(IFNULL(allPayPrice,0)),0) allPayPrice, IFNULL(SUM(IFNULL(platformMoney,0)),0) AS platformMoney, IFNULL(SUM(IFNULL(postmanMoney,0)),0) AS postmanMoney,\n" +
                "IFNULL(SUM(IFNULL(totalGoodsMoney,0)),0) AS totalGoodsMoney, IFNULL(SUM(IFNULL(deliveryPrice,0)),0) AS deliveryPrice, IFNULL(SUM(IFNULL(platformAwardMoney,0)),0) AS platformAwardMoney,\n" +
                "IFNULL(SUM(userCount),0) userCount,\n" +
                "IFNULL(SUM(refundApplyCount),0)  refundApplyCount,IFNULL(SUM(refundApplyMoney),0) refundApplyMoney ");
        sb.append(" FROM ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATISTICS_PAOTUI).append("  a");
        sb.append(" where 1=1 ");
        sb.append(" and oriDate >= '").append(DateUtils.timeStampToDate(startTime)).append("'");
        sb.append(" and oriDate <= '").append(DateUtils.timeStampToDate(endTime)).append("'");

        if (ValidateUtil.isNotNull(city)) {
            sb.append(" and city = '").append(city).append("' ");
        }
        if (ValidateUtil.isNotNull(county)) {
            sb.append(" and county = '").append(county).append("' ");
        }
        if (ValidateUtil.isNotNull(username)) {
            sb.append(" and adminUsername = '").append(username).append("' ");
        }

        StringBuffer sbGroup = new StringBuffer();
        sbGroup.append(" group by oriDate ");
        if (ValidateUtil.isNotNull(city)) {
            sbGroup.append(" ,city");
        }
        if (ValidateUtil.isNotNull(county)) {
            sbGroup.append(" ,county");
        }
        if (ValidateUtil.isNotNull(username)) {
            sbGroup.append(" ,adminUsername");
        }

        List<HashMap<String, Object>> list = null;
        Map<String, Object> info = null;
        try {
//            System.out.println("财务概览-跑腿：\n" + sb.toString() + sbGroup.toString());
            list = UserDaoSql.getInstance().queryBySql(sb.toString() + sbGroup.toString());
            info = UserDaoSql.getInstance().queryMapBySql(sb.toString());
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }

        JYPageUtils.PageList pageList = JYPageUtils.getList(list, page, size);
        // 分页
        list = (List<HashMap<String, Object>>) pageList.list;
        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("totalCount", pageList.totalCount);
        obj.put("data", JSONArray.toJSON(list == null ? Collections.emptyList() : list));
        obj.put("info", JSONObject.toJSON(info));
        return obj.toString();
//        String t_order_info = PojoTables.OrderInfo;
//        String t_order_pay = PojoTables.OrderPay;
//        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
//            String dataDbName = OrderBakTaskUtils.getDataDbName();
//            if (ValidateUtil.isNotNull(dataDbName)) {
//                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
//                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
//            }
//        }

//        // 查询订单情况
//        StringBuffer sb = new StringBuffer();
//        sb.append("SELECT SUM(1) orderCounts, SUM(IFNULL(orp.totalprice,0)) allPayPrice,SUM(IFNULL(orp.platformMoney,0)) AS platformMoney, SUM(IFNULL(orp.postmanMoney,0)) AS postmanMoney, ")
//                .append("SUM(IFNULL(ori.totalGoodsMoney,0)) AS totalGoodsMoney, SUM(IFNULL(ori.deliveryPrice,0)) AS deliveryPrice, SUM(IFNULL(orp.platformAwardMoney,0)) AS platformAwardMoney, ")
//                .append("COUNT(DISTINCT ori.username) userCount, FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS oriDate  ")
//                .append(" FROM ").append(t_order_info).append(" ori ")
//                .append(" LEFT JOIN ").append(t_order_pay).append(" orp ON orp.orderNo = ori.orderNo ");
//        if (ValidateUtil.isNotNull(username)) {
//            sb.append("LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " uar ON uar.agentId=ori.agentId ");
//        }
//        sb.append(" WHERE ori.delFlag=0 AND ori.orderType=").append(ORDER_TYPE.PAO_TUI).append("  AND orp.delFlag=0  ")
//                .append(" AND ori.orderstatus=").append(ORDER_STATUS.OVER).append(" AND ori.isRefundApply!=").append(REFUND_APPLY_STATUS.PASS);
//        if (ValidateUtil.isNotNull(city)) {
//            sb.append(" AND (ori.shopCity='").append(city).append("' OR ori.city='").append(city).append("') ");
//        }
//        if (ValidateUtil.isNotNull(county)) {
//            sb.append(" AND ori.county='").append(county).append("' ");
//        }
//        if (ValidateUtil.isNotNull(username)) {
//            sb.append(" and uar.delFlag=0  ");
//        }
//
//        sb.append(" AND ori.createtime BETWEEN ").append(startTime).append(" AND ").append(endTime).append(" ");
//
//        String sqlGroup = " GROUP BY oriDate DESC";
//        List<HashMap<String, Object>> list = null;
//        Map<String, Object> info = null;
//        try {
//            list = UserDaoSql.getInstance().queryBySql(sb.toString() + sqlGroup);
//            info = UserDaoSql.getInstance().queryMapBySql(sb.toString());
//            System.out.println(sb.toString() + sqlGroup);
//        } catch (SQLException e) {
//            return AjaxResult.errorResult(e.getMessage());
//        }
//        int totalCount = list == null ? 0 : list.size();
//        // 分页
//        JYPageUtils.PageList list1 = JYPageUtils.getList(list, page, size);
//        list = (List<HashMap<String, Object>>) list1.list;
//        JSONObject obj = new JSONObject();
//        obj.put("status", 1);
//        obj.put("totalCount", totalCount);
//        obj.put("data", JSONArray.toJSON(list == null ? Collections.emptyList() : list));
//        obj.put("info", JSONObject.toJSON(info));
//        return obj.toString();
////        return "";

//        String sql = "SELECT SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN 1 ELSE 0 END) orderCounts, " +
//                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.totalprice,0) ELSE 0 END) allPayPrice, " +
//                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformMoney,0) ELSE 0 END) AS platformMoney, " +
//                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) AS postmanMoney, " +
//                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalGoodsMoney,0) ELSE 0 END) AS totalGoodsMoney, " +
//                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.deliveryPrice,0) ELSE 0 END) AS deliveryPrice, " +
//                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformAwardMoney,0) ELSE 0 END) AS platformAwardMoney, " +
//                "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount, " +
//                "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney, " +
//                "COUNT(DISTINCT CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN ori.username ELSE NULL END) userCount, " +
//                "FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS oriDate " +
//                " FROM " + t_order_info + " ori  " +
//                " LEFT JOIN " + t_order_pay + " orp ON orp.orderNo = ori.orderNo AND orp.delFlag=0 " +
//                (StringUtils.isNotEmpty(username) ? ("LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " uar ON uar.agentId=ori.agentId and uar.delFlag=0 ") : "") +
//                " WHERE ori.delFlag=0 AND ori.orderType=" + ORDER_TYPE.PAO_TUI + " " +
//                (StringUtils.isNotEmpty(username) ? (" AND uar.username='" + username + "' ") : "") +
//                (StringUtils.isNotEmpty(city) ? (" AND (ori.shopCity='" + city + "' or ori.city='" + city + "')") : "") +
//                (StringUtils.isNotEmpty(county) ? (" AND ori.county='" + county + "'") : "") +
//                " AND ori.createtime BETWEEN " + startTime + " AND " + endTime + " ";
//        String sqlGroup = "GROUP BY oriDate DESC";
//        List<HashMap<String, Object>> list = null;
//        Map<String, Object> info = null;
//        try {
//            list = UserDaoSql.getInstance().queryBySql(sql + sqlGroup);
//            info = UserDaoSql.getInstance().queryMapBySql(sql);
//        } catch (SQLException e) {
//            return AjaxResult.errorResult(e.getMessage());
//        }
//        int totalCount = list == null ? 0 : list.size();
//        // 分页
//        list = (List<HashMap<String, Object>>) ToolUtils.list(list, (page - 1) * size + 1, page * size);
//        JSONObject obj = new JSONObject();
//        obj.put("status", 1);
//        obj.put("totalCount", totalCount);
//        obj.put("data", JSONArray.toJSON(list == null ? Collections.emptyList() : list));
//        obj.put("info", JSONObject.toJSON(info));
//        return obj.toString();
    }

    /**
     * @param token
     * @param province
     * @param city
     * @param county
     * @param shopId            // 店铺统计时所传店铺id
     * @param orderStatus       // 判断订单状态
     * @param isRefundApply     // 判断是否是退款订单
     * @param startTime         // 时间戳
     * @param endTime
     * @param page
     * @param size
     * @param cityAdminUsername // 代理账号（代理营业统计时所传的某一代理）
     * @param username          // 代理账号（财务概览统计时，如果代理登录时所传的当前登录的代理账号）
     * @return
     */
    // 获取店铺的营收信息每天的详情
    @RequestMapping("admin/money/shop/trade/today/statistics")
    @ResponseBody
    public String shopTradeInfoToDayStatis(String token, String province, String city, String county,
                                           Long shopId, Long squareId, Integer orderStatus, Integer isRefundApply, Integer isExcel,
                                           @RequestParam(defaultValue = "0") Integer isNotShop,
                                           Long startTime, Long endTime, Integer page, Integer size, Integer orderType,
                                           String cityAdminUsername, String username) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代和店铺自己可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.AGENT_MANAGER && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER
                && user.getUserType() != UserType.BUSINESS_MAN) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        if (null == orderType) {
            orderType = 0;
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }
        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sql = "SELECT IFNULL(ori.totalMoney,0) allPayMoney, " +
                "ori.orderNo AS orderNo,ori.shopName AS shopName, ori.orderStatus," +
                " IFNULL(orp.shopRate,0) as shopRate," +
                " IFNULL(orp.squareRate,0) as squareRate, ori.isZiQu, ori.shopSelfPost, " +
                " IFNULL(orp.platformRate,0) as platformRate," +
                " ori.totalGoodsMoney, ori.packetPrice," +
                "IFNULL(orp.totalprice,0) allPayPrice, " +
                "ori.shopAddress, " +
                "ori.address, " +
                "IFNULL(orp.platformMoney,0) AS platformMoney,        " +
                "IFNULL(orp.postCompanyMoney,0) AS postCompanyMoney,        " +
                "IFNULL(orp.squareMoney,0) AS squareMoney,        " +
                "IFNULL(orp.shopMoney,0) AS shopMoney," +
                "IFNULL(orp.agentManagerMoney,0) AS agentManagerMoney," +
                "IFNULL(ori.deliveryPrice,0) AS deliveryPrice," +
                "IFNULL(ori.originalYunFeiShop,0) AS originalYunFeiShop," +
                "IFNULL(ori.originalYunFei,0) AS originalYunFei," +
                "IFNULL(orp.postmanMoney,0) AS postmanMoney," +
                "IFNULL(orp.businessMoney,0) AS businessMoney,      " +
                "IFNULL(orp.platformAwardMoney,0) AS platformAwardMoney, " +
                "IFNULL(orp.shopAwardmoney,0) AS shopAwardmoney,           " +
                "IFNULL((CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalprice ELSE 0 END),0) refundApplyMoney,    " +
                "IFNULL(orp.cityAdminMoney,0) AS cityAdminMoney,     " +
                "IFNULL(orp.cityAdminAward,0) AS cityAdminAward,   " +
                "IFNULL(orp.cityAdminMoney1,0) cityAdminMoney1," +
                "IFNULL(orp.cityAdminMoney2,0) cityAdminMoney2," +
                "IFNULL(orp.cityAdminMoney3,0) cityAdminMoney3," +
                "IFNULL(orp.cityAdminMoney4,0) cityAdminMoney4," +
                "IFNULL(orp.cityAdminAward1,0) cityAdminAward1," +
                "IFNULL(orp.cityAdminAward2,0) cityAdminAward2," +
                "IFNULL(orp.cityAdminAward3,0) cityAdminAward3," +
                "IFNULL(orp.cityAdminAward4,0) cityAdminAward4," +
                "FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d %H:%i:%s') AS oriDate," +
                "ori.createTime AS createTime " +
                "FROM " + t_order_info + " AS ori  " +
                "LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno  and orp.delFlag=0 " +
                (StringUtils.isNotEmpty(username) ? ("LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON ori.agentId=uar.agentId and uar.delFlag =0 ") : "") +
                ((user.getUserType() == UserType.AGENT_MANAGER) ?
                        " LEFT JOIN " + PojoTables.SHOP_AGENT_RELATION + " AS sar ON sar.shopId=ori.shopId and sar.delFlag =0 " +
                                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=sar.agentId and cag.delFlag =0  " : " "
                ) +
                // "WHERE ori.delFlag=0 and ori.orderNo not like 'S%' " +
                " WHERE ori.delFlag=0 " + (ValidateUtil.isID(shopId) ? "" : " and ori.orderNo not like 'S%' ") +
                (null != orderType && orderType != 0 ? (" AND ori.orderType=" + orderType + "") :
                        (isNotShop == 1 ? "" : " and ori.shopId > 0 ")) +
                (StringUtils.isNotEmpty(username) ? (" and uar.delFlag=0 ") : "") +
                (cityAdminUsername != "" && StringUtils.isNotEmpty(cityAdminUsername) ? (" AND (orp.cityAdmin1 = " + cityAdminUsername +
                        " OR orp.cityAdmin2 = " + cityAdminUsername +
                        " OR orp.cityAdmin3 = " + cityAdminUsername +
                        " OR orp.cityAdmin4 = " + cityAdminUsername +
                        " OR orp.cityAdmin = " + cityAdminUsername +
                        ")") : "") +
                (shopId != null ? "AND ori.shopId=" + shopId : " ") +
                (squareId != null ? "AND ori.squareShopId=" + squareId : " ") +
                (StringUtils.isNotEmpty(city) ? (" AND ori.shopCity='" + city + "'") : "") +
                (StringUtils.isNotEmpty(username) ? (" AND uar.username='" + username + "'") : "") +
                (user.getUserType() == UserType.AGENT_MANAGER ? (" AND cag.agentManagerUsername = '" + user.getUsername() + "'") : "") +
                " And ori.createtime BETWEEN " + startTime + " AND " + endTime +
                (orderStatus != null ? (" and ori.orderStatus=" + orderStatus + "") : "") +
                (isRefundApply != null ? (" and (ori.isRefundApply=" + isRefundApply + " or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + ")") : "") +
                " GROUP BY ori.orderno" +
                " ORDER BY oriDate ";
        List<HashMap<String, Object>> lists = null;
        try {
            lists = UserDaoSql.getInstance().queryBySql(sql);

        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
//        ShopTradeInfoVo info = null;
//        if (lists != null && !lists.isEmpty()) {
//            info = new ShopTradeInfoVo();
//            for (int i = 0; i < lists.size(); i++) {
//                ShopTradeInfoVo a = lists.get(i);
//                info.setAllPayMoney(a.getAllPayMoney() + info.getAllPayMoney());
//                info.setAllPayPrice(a.getAllPayPrice() + info.getAllPayPrice());
//                info.setOrderCounts(a.getOrderCounts() + info.getOrderCounts());
//                info.setOrderCounts2(a.getOrderCounts2() + info.getOrderCounts2());
//                info.setRefundApplyCount(a.getRefundApplyCount() + info.getRefundApplyCount());
//                info.setRefundApplyMoney(a.getRefundApplyMoney() + info.getRefundApplyMoney());
//                info.setCityAgentMoney(a.getCityAgentMoney() + info.getCityAgentMoney());
//                info.setShopAwardMoney(a.getShopAwardMoney() + info.getShopAwardMoney());
//                info.setShopMoney(a.getShopMoney() + info.getShopMoney());
//                info.setPlatformAwardMoney(a.getPlatformAwardMoney() + info.getPlatformAwardMoney());
//                info.setPlatformMoney(a.getPlatformMoney() + info.getPlatformMoney());
//                info.setPostmanMoney(a.getPostmanMoney() + info.getPostmanMoney());
//                info.setUserCount(a.getUserCount() + info.getUserCount());
//            }
//        }

        if (null == isExcel) {
            isExcel = 0;
        }
        if (isExcel == 1) {
            // 导出excel
            if (lists != null) {
                String downloadUrl = everyDayOrderExcel(lists);
                if (ValidateUtil.isNotNull(downloadUrl)) {
                    if (ValidateUtil.isNotNetUrl(downloadUrl)) {
                        downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                    }
                    return AjaxResult.successResult(downloadUrl);
                }
            }
        } else {
            int totalCount = lists == null ? 0 : lists.size();
//        List<HashMap<String, Object>> list = null;
            // 分页
            lists = (List<HashMap<String, Object>>) ToolUtils.list(lists, (page - 1) * size + 1, page * size);

            JSONObject obj = new JSONObject();
            obj.put("status", 1);
            obj.put("totalCount", totalCount);
            obj.put("data", JSONArray.toJSON(lists == null ? Collections.emptyList() : lists));
//        obj.put("info", JSONObject.toJSON(info));
            return obj.toString();
        }
        return AjaxResult.successResult(0, 0, null);
    }

    //---------------------------------生成每天订单统计Excel
    private String everyDayOrderExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "时间"));
            sheet.addCell(new Label(col++, row, "订单号"));
            sheet.addCell(new Label(col++, row, "店铺名"));
            sheet.addCell(new Label(col++, row, "平台收入"));
            sheet.addCell(new Label(col++, row, "店铺收入"));
            sheet.addCell(new Label(col++, row, "代理收入"));
            sheet.addCell(new Label(col++, row, "配送员收入"));
            if (SYS_SETTINGS.CUSTOM_SYSTEM_TYPE != null && SYS_SETTINGS.CUSTOM_SYSTEM_TYPE == 1) {
                sheet.addCell(new Label(col++, row, "配送公司收入"));
                sheet.addCell(new Label(col++, row, "创业商圈收入"));
            }
            sheet.addCell(new Label(col++, row, "支付金额（不含退款）"));
            sheet.addCell(new Label(col++, row, "商品金额"));
            sheet.addCell(new Label(col++, row, "配送费"));
//            sheet.addCell(new Label(col++, row, "店铺优惠配送费"));
//            sheet.addCell(new Label(col++, row, "代理优惠配送费"));
            sheet.addCell(new Label(col++, row, "打包费"));
            sheet.addCell(new Label(col++, row, "订单方式"));
            sheet.addCell(new Label(col++, row, "平台优惠"));
            sheet.addCell(new Label(col++, row, "店铺优惠"));
            sheet.addCell(new Label(col++, row, "代理优惠"));
            sheet.addCell(new Label(col++, row, "退款金额"));
            sheet.addCell(new Label(col++, row, "平台分成比例"));
            sheet.addCell(new Label(col++, row, "店铺分成比例"));
            row = 1;
            for (Map<String, Object> record : list) {
                col = 0;
                double totalPrice = 0;
                sheet.addCell(new Label(col++, row, record.get("oriDate").toString()));
                sheet.addCell(new Label(col++, row, record.get("orderNo").toString()));
                sheet.addCell(new Label(col++, row, record.get("shopName").toString()));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("platformMoney").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("shopMoney").toString())));

                float cityAdminMoney = Float.parseFloat(record.get("cityAdminMoney").toString());
                float cityAdminMoney1 = Float.parseFloat(record.get("cityAdminMoney1").toString());
                float cityAdminMoney2 = Float.parseFloat(record.get("cityAdminMoney2").toString());
                float cityAdminMoney3 = Float.parseFloat(record.get("cityAdminMoney3").toString());
                float cityAdminMoney4 = Float.parseFloat(record.get("cityAdminMoney4").toString());
                sheet.addCell(new Number(col++, row, cityAdminMoney + cityAdminMoney1 + cityAdminMoney2 + cityAdminMoney3 + cityAdminMoney4));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("postmanMoney").toString())));
                if (SYS_SETTINGS.CUSTOM_SYSTEM_TYPE != null && SYS_SETTINGS.CUSTOM_SYSTEM_TYPE == 1) {
                    sheet.addCell(new Number(col++, row, JYMathDoubleUtils.scaleHalfUp(Float.parseFloat(record.get("postCompanyMoney").toString()))));
                    sheet.addCell(new Number(col++, row, JYMathDoubleUtils.scaleHalfUp(Float.parseFloat(record.get("squareMoney").toString()))));
                }

                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("allPayPrice").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("totalGoodsMoney").toString())));

                float originalYunFei = Float.parseFloat(record.get("originalYunFei").toString());
                float originalYunFeiShop = Float.parseFloat(record.get("originalYunFeiShop").toString());
                float deliveryPrice = Float.parseFloat(record.get("deliveryPrice").toString());
                float cityAdminYunFei = originalYunFei - originalYunFeiShop - deliveryPrice > 0 ? originalYunFei - originalYunFeiShop - deliveryPrice : 0;


                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("originalYunFei").toString())));
//                sheet.addCell(new Label(col++, row, record.get("originalYunFeiShop").toString()));
//                sheet.addCell(new Number(col++, row, originalYunFei - originalYunFeiShop - deliveryPrice));

                float shopAwardmoney = Float.parseFloat(record.get("shopAwardmoney").toString());
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("packetPrice").toString())));
                if (record.get("extra1") != null && "scan_code".equals(record.get("extra1").toString())) {
                    sheet.addCell(new Label(col++, row, "扫码单"));
                } else if (record.get("isZiQu") != null && Integer.parseInt(record.get("isZiQu").toString()) == 1) {
                    sheet.addCell(new Label(col++, row, "自取单"));
                } else {
                    sheet.addCell(new Label(col++, row, "配送单"));
                }
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("platformAwardMoney").toString())));
                sheet.addCell(new Number(col++, row, shopAwardmoney + originalYunFeiShop));
                float cityAdminAward = Float.parseFloat(record.get("cityAdminAward").toString());
                float cityAdminAward1 = Float.parseFloat(record.get("cityAdminAward1").toString());
                float cityAdminAward2 = Float.parseFloat(record.get("cityAdminAward2").toString());
                float cityAdminAward3 = Float.parseFloat(record.get("cityAdminAward3").toString());
                float cityAdminAward4 = Float.parseFloat(record.get("cityAdminAward4").toString());
                sheet.addCell(new Number(col++, row, cityAdminAward + cityAdminAward1 + cityAdminAward2 + cityAdminAward3 + cityAdminAward4 + cityAdminYunFei));

                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("refundApplyMoney").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("platformRate").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("shopRate").toString())));
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }

    // 获取店铺的营业统计-计算不同平台的销售额和订单量
    @RequestMapping("admin/money/shop/order-money/statistics")
    @ResponseBody
    public String shopTradeStatis2(String token, Long shopId, String province, String city, String county, Long startTime, Long endTime, Integer page, Integer size) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代和店铺自己可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.SHOP_OWNER) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (user.getUserType() == UserType.SHOP_OWNER) {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopByUsername(user.getUsername());
            shopId = shopInfo.getId();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        /*
            SELECT FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') ordate,
                COUNT(ori.orderno) orderCount,
                SUM(CASE WHEN ori.payType='alipay' THEN 1 ELSE 0 END) aliCount,
                SUM(CASE WHEN ori.payType='wx' THEN 1 ELSE 0 END) wxCount,
                SUM(CASE WHEN ori.payType='wx_xcx' THEN 1 ELSE 0 END) wxxCount,
                SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN 1 ELSE 0 END) othCount,
                SUM(ori.totalPrice) totalPrice,
                SUM(CASE WHEN ori.payType='alipay' THEN ori.totalPrice ELSE 0 END) aliPrice,
                SUM(CASE WHEN ori.payType='wx' THEN ori.totalPrice ELSE 0 END) wxPrice,
                SUM(CASE WHEN ori.payType='wx_xcx' THEN ori.totalPrice ELSE 0 END) wxxPrice,
                SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN ori.totalPrice ELSE 0 END) othPrice
            FROM order_info AS ori
            LEFT JOIN user_agent_relation AS uar ON uar.agentid=ori.agentId
            WHERE ori.delFlag=0 AND ori.orderStatus=9
            GROUP BY ordate
         */

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sqlTotal = "SELECT " +
                "           COUNT(ori.orderno) orderCount, " +
                "           SUM(CASE WHEN ori.payType='alipay' THEN 1 ELSE 0 END) aliCount, " +
                "           SUM(CASE WHEN ori.payType='wx' THEN 1 ELSE 0 END) wxCount, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' THEN 1 ELSE 0 END) wxxCount, " +
                "           SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN 1 ELSE 0 END) othCount, " +
                "           SUM(ori.totalPrice) totalPrice, " +
                "           SUM(CASE WHEN ori.payType='alipay' THEN ori.totalPrice ELSE 0 END) aliPrice, " +
                "           SUM(CASE WHEN ori.payType='wx' THEN ori.totalPrice ELSE 0 END) wxPrice, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' THEN ori.totalPrice ELSE 0 END) wxxPrice, " +
                "           SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN ori.totalPrice ELSE 0 END) othPrice " +
                " FROM " + t_order_info + " AS ori " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentid=ori.agentId and uar.delFlag =0 " +
                " LEFT JOIN " + PojoTables.ShopInfo + " as sfo on sfo.id=ori.shopId and sfo.delFlag =0 " +
                " WHERE ori.delFlag=0 AND ori.orderStatus=9  " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                (shopId != null ? " AND ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.username='" + user.getUsername() + "'") : "");

        String sql = "SELECT FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') ordate, " +
                "           COUNT(ori.orderno) orderCount, " +
                "           SUM(CASE WHEN ori.payType='alipay' THEN 1 ELSE 0 END) aliCount, " +
                "           SUM(CASE WHEN ori.payType='wx' THEN 1 ELSE 0 END) wxCount, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' THEN 1 ELSE 0 END) wxxCount, " +
                "           SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN 1 ELSE 0 END) othCount, " +
                "           SUM(ori.totalPrice) totalPrice, " +
                "           SUM(CASE WHEN ori.payType='alipay' THEN ori.totalPrice ELSE 0 END) aliPrice, " +
                "           SUM(CASE WHEN ori.payType='wx' THEN ori.totalPrice ELSE 0 END) wxPrice, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' THEN ori.totalPrice ELSE 0 END) wxxPrice, " +
                "           SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN ori.totalPrice ELSE 0 END) othPrice " +
                " FROM " + t_order_info + "order_info AS ori  " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentid=ori.agentId and uar.delFlag =0 " +
                " LEFT JOIN " + PojoTables.ShopInfo + " as sfo on sfo.id=ori.shopId and sfo.delFlag =0 " +
                " WHERE ori.delFlag=0 AND ori.orderStatus=9  " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                (shopId != null ? " AND ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.username='" + user.getUsername() + "'") : "") +
                " GROUP BY ordate " +
                " LIMIT " + ((page - 1) * size) + "," + size;
        String sqlCount = "SELECT COUNT(*) COUNTs FROM(SELECT FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') ordate, " +
                "           COUNT(ori.orderno) orderCount, " +
                "           SUM(CASE WHEN ori.payType='alipay' THEN 1 ELSE 0 END) aliCount, " +
                "           SUM(CASE WHEN ori.payType='wx' THEN 1 ELSE 0 END) wxCount, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' THEN 1 ELSE 0 END) wxxCount, " +
                "           SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN 1 ELSE 0 END) othCount, " +
                "           SUM(ori.totalPrice) totalPrice, " +
                "           SUM(CASE WHEN ori.payType='alipay' THEN ori.totalPrice ELSE 0 END) aliPrice, " +
                "           SUM(CASE WHEN ori.payType='wx' THEN ori.totalPrice ELSE 0 END) wxPrice, " +
                "           SUM(CASE WHEN ori.payType='wx_xcx' THEN ori.totalPrice ELSE 0 END) wxxPrice, " +
                "           SUM(CASE WHEN ori.payType!='alipay' AND ori.payType!='wx' AND ori.payType!='wx_xcx' THEN ori.totalPrice ELSE 0 END) othPrice " +
                " FROM " + t_order_info + " AS ori  " +
                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentid=ori.agentId and uar.delFlag =0 " +
                " LEFT JOIN " + PojoTables.ShopInfo + " as sfo on sfo.id=ori.shopId and sfo.delFlag =0 " +
                " WHERE ori.delFlag=0 AND ori.orderStatus=9  " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                (shopId != null ? " AND ori.shopId=" + shopId : "") +
                (StringUtils.isNotEmpty(province) ? (" AND sfo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND sfo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uar.username='" + user.getUsername() + "'") : "") +
                " GROUP BY ordate )a";
        List<HashMap<String, Object>> list = null;
        HashMap<String, Object> info = null;
        long totalCount = 0;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            info = UserDaoSql.getInstance().queryMapBySql(sqlTotal);
            totalCount = UserDaoSql.getInstance().getCount(sqlCount);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (list != null && !list.isEmpty()) {

        }
        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("totalCount", totalCount);
        obj.put("data", JSONArray.toJSON(list == null ? Collections.emptyList() : list));
        obj.put("info", JSONObject.toJSON(info));
        return obj.toString();
    }

    /**
     * 用户消费记录导出
     *
     * @param token     唯一标识
     * @param province  省份
     * @param city      城市
     * @param county    区县
     * @param startTime 支付开始时间
     * @param endTime   支付结束时间
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "admin/user/pay/list")
    @ResponseBody
    public String userConsumption(String token, String province, String city, String county, Long startTime, Long endTime) {

        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sql = "SELECT username,totalCount,totalPrice,payType," +
                "FROM_UNIXTIME(payTime/1000,'%Y-%m-%d %H:%i:%s') AS payTime," +
                "buyer,telephone,province,city,county,address " +
                "FROM order_info WHERE " +
                (StringUtils.isNotEmpty(province) ? (" province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND county='" + county + "'") : "") +
                " AND payTime BETWEEN  " + startTime +
                " AND " + endTime +
                " AND isRefundApply = 0 AND isPaid = 1 AND orderStatus = 9 ORDER BY totalPrice DESC";
        List<HashMap<String, Object>> lists = null;
        try {
            lists = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("data", JSONArray.toJSON(lists == null ? Collections.emptyList() : lists));

        String downloadUrl = generateWalletPopExcel3(lists);
        if (ValidateUtil.isNotNull(downloadUrl)) {
            return AjaxResult.successResult(SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl);
        }
        return obj.toString();
    }

    private String generateWalletPopExcel3(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.WALLET_POP + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "用户账号"));
            sheet.addCell(new Label(col++, row, "用户名"));
            sheet.addCell(new Label(col++, row, "联系方式"));
            sheet.addCell(new Label(col++, row, "省份"));
            sheet.addCell(new Label(col++, row, "城市"));      //  ?
            sheet.addCell(new Label(col++, row, "区域"));
            sheet.addCell(new Label(col++, row, "详细地址"));
            sheet.addCell(new Label(col++, row, "购买数量"));
            sheet.addCell(new Label(col++, row, "购买金额"));
            sheet.addCell(new Label(col++, row, "支付类型"));
            sheet.addCell(new Label(col++, row, "支付时间"));
            row = 1;
            for (int i = 0; i < list.size(); i++) {
                col = 0;
                String channel = "";

                sheet.addCell(new Label(col++, row, list.get(i).get("username").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("buyer").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("telephone").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("province").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("city").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("county").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("address").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("totalCount").toString()));
                sheet.addCell(new Label(col++, row, list.get(i).get("totalPrice").toString()));
                switch (list.get(i).get("payType").toString()) {
                    case "arrive":
                        // 货到付款
                        sheet.addCell(new Label(col++, row, "货到付款"));
                        break;
                    case "alipay":
                        // 支付宝
                        sheet.addCell(new Label(col++, row, "支付宝"));
                        break;
                    case "wx":
                        // 微信
                        sheet.addCell(new Label(col++, row, "微信"));
                        break;
                    case "alipay_qr":
                        // 支付宝收款二维码
                        sheet.addCell(new Label(col++, row, "支付宝收款二维码"));
                        break;
                    case "wx_pub_qr":
                        // 微信支付收款二维码
                        sheet.addCell(new Label(col++, row, "微信支付收款二维码"));
                        break;
                    case "wx_gzh":
                        // 微信公众号
                        sheet.addCell(new Label(col++, row, "微信公众号"));
                        break;
                    case "wallet":
                        // 钱包余额
                        sheet.addCell(new Label(col++, row, "钱包余额"));
                        break;
                    case "bankCard":
                        // 银行卡
                        sheet.addCell(new Label(col++, row, "银行卡"));
                        break;
                }

                sheet.addCell(new Label(col++, row, list.get(i).get("payTime").toString()));

                row++;     // 行
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }

    // 创业广场下拉
    @RequestMapping("/admin/square/shop/select")
    @ResponseBody
    public String admin_square_select(String token, String county, String city) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        String agent = " left join shop_agent_relation sar on sar.shopId=sfo.id and sar.delFlag=0 " +
                " left join user_agent_relation uar on uar.agentId=sar.agentId and uar.delFlag=0 ";
        String sql = "select DISTINCT sfo.* FROM square_shop_sign sss " +
                "LEFT JOIN square_info si ON si.id = sss.squareId AND si.delFlag=0 " +
                "LEFT JOIN shop_info AS sfo ON sfo.id = si.shopId AND sfo.delFlag=0 " +
                (user.getUserType() == UserType.ADMIN ? agent : "") +
                "WHERE sss.delFlag=0 " +
                (ValidateUtil.isNotNull(city) ? (" AND sfo.city='" + city + "'") : "") +
                (ValidateUtil.isNotNull(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? " and uar.username=" + user.getUsername() + " " : "");
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (Exception e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        return AjaxResult.successResult(list);
    }

    // 管理后台，创业广场统计
    @RequestMapping(method = RequestMethod.POST, value = "admin/money/square/statistics")
    @ResponseBody
    public String squareStatis(String token, String province, String county, String city, Integer orderBy, Integer orderType,
                               Integer shopRole, Long startTime, Long endTime, Long shopId, Long squareId,
                               Integer page, Integer size, Integer isExcel) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.AGENT_MANAGER && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_COMPANY && user.getUserType() != UserType.SUPER_ADMIN) {
            return AjaxResult.successResult(null, 0);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }

//        店铺名称 店铺地址 订单数量 营业额 客单价 店铺补贴 平台补贴 退款订单量 退款订单金额 可提现金额 累计营业额 累计已提现

        String agent = " left join shop_agent_relation sar on sar.shopId=sfo.id and sar.delFlag=0 " +
                " left join user_agent_relation uar on uar.agentId=sar.agentId and uar.delFlag=0 ";
//         商品金额 支付金额 商家收入 代理收益 代理铺贴
        String sql = "SELECT DISTINCT sfo.id,sfo.shopname,sfo.id AS squareId,sfo.ownUsername,sfo.shopRole,       " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalprice,0) ELSE 0 END) prices,       " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " or ori.orderstatus=" + ORDER_STATUS.PAYED_CANCEL + " THEN 1 ELSE 0 END) as  orderCounts,       " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.shopAwardMoney,0) ELSE 0 END) shopAwardMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformAwardMoney,0) ELSE 0 END) platformAwardMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformMoney,0) ELSE 0 END) platformMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalGoodsMoney,0) ELSE 0 END) totalGoodsMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.deliveryPrice,0) ELSE 0 END) deliveryPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.originalYunFeiShop,0) ELSE 0 END) AS originalYunFeiShop,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.originalYunFei,0) ELSE 0 END) AS originalYunFei,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) postmanMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.packetPrice,0) ELSE 0 END) packetPrice,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.shopMoney,0) ELSE 0 END) shopMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.squareMoney,0) ELSE 0 END) squareMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postCompanyMoney,0) ELSE 0 END) postCompanyMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND orp.isOrderCancel=0 THEN IFNULL(orp.agentManagerMoney,0) ELSE 0 END) agentManagerMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney,0) ELSE 0 END) cityAdminMoney,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward,0) ELSE 0 END) cityAdminAward,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney1,0) ELSE 0 END) cityAdminMoney1,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney2,0) ELSE 0 END) cityAdminMoney2,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney3,0) ELSE 0 END) cityAdminMoney3,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney4,0) ELSE 0 END) cityAdminMoney4,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward1,0) ELSE 0 END) cityAdminAward1,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward2,0) ELSE 0 END) cityAdminAward2,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward3,0) ELSE 0 END) cityAdminAward3,        " +
                "SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward4,0) ELSE 0 END) cityAdminAward4,        " +
                "IFNULL(uwt.money,0) money,       IFNULL(uwt.totalMoney,0) totalMoney,       IFNULL(uwt.popTotalMoney,0) popTotalMoney,        " +
                "IFNULL(uwt.popMoney,0) popMoney,       " +
                // "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount,        " +
                "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount,        " +
                // "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney  " +
                "SUM(CASE WHEN ori.isRefundApply=" + REFUND_APPLY_STATUS.PASS + " OR ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney  " +
                // "FROM square_shop_sign sss " +
                // "LEFT JOIN square_info si ON si.id = sss.squareId AND si.delFlag=0 " +
                // "LEFT JOIN shop_info AS sfo ON sfo.id = si.shopId AND sfo.delFlag=0 " +

                " from " + PojoTables.SQUARE_INFO + " si " +
                "LEFT JOIN shop_info AS sfo ON sfo.id = si.shopId AND sfo.delFlag=0 " +
                (user.getUserType() == UserType.ADMIN ? agent : "") +
                "LEFT JOIN user_wallet AS uwt ON uwt.username=sfo.ownUsername  AND uwt.delFlag =0   " +
                "LEFT JOIN order_info AS ori ON ori.squareShopId=sfo.id AND ori.ordertype!=5 AND ori.delFlag=0 " +
                "AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                " LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno  AND orp.delFlag=0 " +
                // "WHERE sss.delFlag=0  " +
                "WHERE si.delFlag=0  " +
                (null != squareId ? (" AND sfo.id=" + squareId + "") : "") +
                (ValidateUtil.isNotNull(city) ? (" AND sfo.city='" + city + "'") : "") +
                (ValidateUtil.isNotNull(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? " and uar.username=" + user.getUsername() + " " : "") +
                // " GROUP BY sss.id " +
                " GROUP BY si.id " +
                " ORDER BY " + (orderBy == null || orderBy == 1 ? "prices " : "") + (orderType == null || orderType == 1 ? " DESC" : " ASC");
        if (null == isExcel) {
            isExcel = 0;
        }
        if (0 == 1 - isExcel) {
        } else {
            sql += " LIMIT " + ((page - 1) * size) + "," + size;
        }


        // 总行数
        String sqlCount = "select count(a.id) counts from (SELECT DISTINCT sfo.id id " +
                // "FROM square_shop_sign sss " +
                // "LEFT JOIN square_info si ON si.id = sss.squareId AND si.delFlag=0 " +
                // "LEFT JOIN shop_info AS sfo ON sfo.id = si.shopId AND sfo.delFlag=0 " +
                "FROM " + PojoTables.SQUARE_INFO + " si  " +
                "LEFT JOIN shop_info AS sfo ON sfo.id = si.shopId AND sfo.delFlag=0 " +
                (user.getUserType() == UserType.ADMIN ? agent : "") +
                "LEFT JOIN user_wallet AS uwt ON uwt.username=sfo.ownUsername  AND uwt.delFlag =0   " +
                "LEFT JOIN order_info AS ori ON ori.squareShopId=sfo.id AND ori.ordertype!=5 AND ori.delFlag=0 " +
                "AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                " LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno  AND orp.delFlag=0 " +
                // "WHERE sss.delFlag=0  " +
                "WHERE si.delFlag=0  " +
                (null != squareId ? (" AND sfo.id=" + squareId + "") : "") +
                (ValidateUtil.isNotNull(city) ? (" AND sfo.city='" + city + "'") : "") +
                (ValidateUtil.isNotNull(county) ? (" AND sfo.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? " and uar.username=" + user.getUsername() + " " : "") +
                // " GROUP BY sss.id ) a";
                " GROUP BY si.id ) a";
        List<HashMap<String, Object>> list = null;
        long totalCount = 0;
        HashMap<String, Object> map = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);

            // 导出时不需要查询中条数
            if (0 == 1 - isExcel) {
            } else {
                totalCount = UserDaoSql.getInstance().getCount(sqlCount);
            }
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                // 计算客单价
                int count = Integer.parseInt(list.get(i).get("orderCounts").toString());
                double prices = Double.parseDouble(list.get(i).get("prices").toString());
                list.get(i).put("price", count <= 0 ? 0D : prices / count);
            }
        }


        if (isExcel == 1) {
            // 导出excel
//            String downloadUrl = shopStatisExcel(list);
//            if (ValidateUtil.isNotNull(downloadUrl)) {
//                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
//                return AjaxResult.successResult(url);
//            }
        } else {
            return AjaxResult.successResult(list, totalCount);
        }
        return AjaxResult.successResult(0, 0, null);

    }

    // 船业商圈下-订单下的抽成详情问题
    @RequestMapping(method = RequestMethod.POST, value = "admin/money/square/shop/money/detail")
    @ResponseBody
    public String square_shop_money_detal(String orderNo) {
        if (ValidateUtil.isNull(orderNo)) {
            return AjaxResult.successResult(0, null);
        }
        String sql = "SELECT \n" +
                "  ori.orderNo,\n" +
                "  IFNULL(orp.`shopMoney`, 0) AS shopMoney ,\n" +
                "  IFNULL(orp.`totalprice`, 0) AS totalPrice ,\n" +
                "  si.ownUsername AS ownusername,\n" +
                "  (IFNULL(orp.`totalprice`, 0) - IFNULL(orp.`shopMoney`, 0) ) AS squareMoney,\n" +
                "  IFNULL(si.`shopName`,'') AS shopName \n" +
                "FROM\n" +
                "  " + PojoTables.OrderInfo + " AS ori \n" +
                "  LEFT JOIN " + PojoTables.OrderPay + " AS orp \n" +
                "    ON ori.`orderNo` = orp.orderNo \n" +
                "  LEFT JOIN " + PojoTables.ShopInfo + " AS si \n" +
                "    ON si.id = ori.shopId \n" +
                "    AND si.delFlag = 0 \n" +
                "    AND ori.delFlag = 0 \n" +
                "    AND orp.delFlag = 0 \n" +
                " WHERE parentOrderNo =  '" + orderNo + "'";
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (ValidateUtil.isAbsList(list)) {
            return AjaxResult.successResult(list.size(), list);
        }
        return AjaxResult.successResult(0, null);
    }


    // paotuiStatics
    @RequestMapping(method = RequestMethod.POST, value = "admin/statistic/order/paotui")
    @ResponseBody
    public String paotuiStatics(Long startTime, Long endTime, Boolean dataDb) {
        if (null == dataDb) {
            dataDb = false;
        }
        OrderStatisticsUtils.paotuiStatics(null, null, dataDb);
        return AjaxResult.successResult();
    }


    /**
     * 手动触发订单账单 统计
     *
     * @param user
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "/admin/shop/order/statement/statistics/strike")
    @ResponseBody
    public String shopOrderStatementStatistic(@LoginUser UserInfo user) {
        if (!User.hasSuperAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("权限不足");
        }
        Set<Integer> failSet = OrderStatisticsUtils.statementOrderStatistics(false, user.getUsername());
        if (failSet == null || failSet.isEmpty()) {
            return AjaxResult.successResult();
        }
        return AjaxResult.errorResult("部分统计失败[" + StringUtils.join(failSet, ",") + "]");
    }


    /**
     * 查询账单
     */
    @RequestMapping(method = RequestMethod.POST, value = "/admin/shop/order/statement/statistics/search")
    @ResponseBody
    public String shopOrderStatementStatisticSearch(@LoginUser UserInfo user, HttpServletResponse response, Long agentId, String cityAdmin, Integer settlementType, Integer statementState,
                                                    Long startTime, Long endTime, String content,
                                                    Integer isExcel, Integer page, Integer size) {
        if (!User.hasSuperAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("权限不足");
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 20;
        }
        StringBuilder contentSql = new StringBuilder();
        /**
         * SELECT
         *   OS.*,
         *   S.shopName,
         *   sc.username
         * FROM
         *   order_statement_statistics AS OS
         *   LEFT JOIN SHOP_INFO AS S ON OS.`shopId` = S.id
         *   LEFT JOIN user_agent_relation AS UA ON UA.agentId=OS.agentId AND UA.delFlag = 0
         *   LEFT JOIN SYS_CITY_ADMIN AS SC ON UA.username = sc.username AND sc.delFlag = 0
         * WHERE OS.delFlag = 0
         * AND OS.`settlementType` = 1
         * AND OS.`statementState` = 0
         * AND S.shopName LIKE '%阿水%'
         * AND OS.`statementTime` BETWEEN 0 AND 1606115883000
         */
        contentSql.append(" FROM ").append(PojoTables.ORDER_STATEMENT_STATISTICS).append(" AS OS ")
                .append(" LEFT JOIN ").append(PojoTables.ShopInfo).append(" AS S ON OS.`shopId` = S.id ");
        contentSql.append("  LEFT JOIN ").append(PojoTables.CITY_AGENT).append(" AS CA ON CA.id=OS.agentId ");
        contentSql.append("  LEFT JOIN ").append(PojoTables.USER_AGENT_RELATION).append(" AS UA ON UA.agentId=OS.agentId AND UA.delFlag = 0 ")
                .append("  LEFT JOIN ").append(PojoTables.SysCityAdmin).append(" AS SC ON UA.username = sc.username AND sc.delFlag = 0 ");
        contentSql.append(" where OS.delFlag = 0 ");
        if (settlementType != null) {
            contentSql.append(" AND OS.`settlementType` = ").append(settlementType);
        }
        if (statementState != null) {
            contentSql.append(" AND OS.`statementState` = ").append(statementState);
        }
        if (ValidateUtil.isNotNull(content)) {
            contentSql.append(" AND S.shopName LIKE '%").append(content).append("%' ");
        }
        if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
            contentSql.append(" AND  OS.`statementTime` BETWEEN ").append(startTime).append(" AND ").append(endTime);
        }
        if (ValidateUtil.isID(agentId)) {
            contentSql.append(" AND OS.agentId = ").append(agentId).append(" ");
        }
        if (ValidateUtil.isNotID(agentId) && ValidateUtil.isNotNull(cityAdmin)) {
            contentSql.append(" AND SC.username = ").append(cityAdmin).append(" ");
        }
        String selectSql = " select OS.* , S.shopName,CA.name as agentName ,SC.username as cityAdmin " + contentSql.toString() + " order by OS.id desc ";
        if (isExcel != null && isExcel == 1) {

        } else {
            selectSql += "  limit " + (page - 1) * size + ", " + size;
        }
        String countSql = " select  count(*) as counts " + contentSql.toString();
        List<OrderStatementStatisticsVO> list = null;
        long count = 0;
        try {
            if (isExcel != null && isExcel == 1) {
                count = UserDaoSql.getInstance().getCount(countSql);
            }
            System.out.println("sql--->" + selectSql.toString());
            list = (List<OrderStatementStatisticsVO>) UserDaoSql.getInstance().list(OrderStatementStatisticsVO.class, selectSql);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (ValidateUtil.isAbsList(list)) {
            list.forEach(item -> {
                if (ValidateUtil.isNotNull(item.getCityAdmin())) {
                    UserInfo info = UserInfoMemory.getInstance().getUserInfo(item.getCityAdmin());
                    if (info != null && ValidateUtil.isNotNull(info.getName())) {
                        item.setCityAdminName(info.getName());
                    }
                }
            });
        }

        if (isExcel != null && isExcel == 1) {
            String path = shopOrderStatementStatisticsExcel(list);
            if (ValidateUtil.isNotNull(path)) {
                if (ValidateUtil.isNotNetUrl(path)) {
                    path = SYS_CONFIG_PATH.IMAGE_BASE_URL + path;
                }
                return AjaxResult.successResult(path);
            } else {
                return AjaxResult.errorResult("导出失败,清稍后重试");
            }
        } else {
            return AjaxResult.successResult(list, count);
        }
    }

    // 导出账单的
    private String shopOrderStatementStatisticsExcel(List<OrderStatementStatisticsVO> list) {
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "商圈名称"));
            sheet.addCell(new Label(col++, row, "代理账号"));
            sheet.addCell(new Label(col++, row, "代理名称"));
            sheet.addCell(new Label(col++, row, "商家名称"));
            sheet.addCell(new Label(col++, row, "商家收入"));
            sheet.addCell(new Label(col++, row, "配送员收入"));
            sheet.addCell(new Label(col++, row, "代理收入"));
            sheet.addCell(new Label(col++, row, "平台收入"));
            sheet.addCell(new Label(col++, row, "支付金额"));
            sheet.addCell(new Label(col++, row, "商家优惠金额"));
            sheet.addCell(new Label(col++, row, "平台优惠金额"));
            sheet.addCell(new Label(col++, row, "代理优惠金额"));
            sheet.addCell(new Label(col++, row, "订单数量"));

            sheet.addCell(new Label(col++, row, "结算类型"));
            sheet.addCell(new Label(col++, row, "结算状态"));
            sheet.addCell(new Label(col++, row, "结算日"));
            sheet.addCell(new Label(col++, row, "审核者"));
            sheet.addCell(new Label(col++, row, "审核时间"));
            sheet.addCell(new Label(col++, row, "是否系统自动创建"));
            sheet.addCell(new Label(col++, row, "创建者"));
            row = 1;
            if (ValidateUtil.isAbsList(list)) {
                for (OrderStatementStatisticsVO record : list) {
                    col = 0;
                    sheet.addCell(new Label(col++, row, ValidateUtil.isNull(record.getAgentName()) ? "" : record.getAgentName()));
                    sheet.addCell(new Label(col++, row, ValidateUtil.isNull(record.getCityAdmin()) ? "" : record.getCityAdmin()));
                    sheet.addCell(new Label(col++, row, ValidateUtil.isNull(record.getCityAdminName()) ? "" : record.getCityAdminName()));
                    sheet.addCell(new Label(col++, row, ValidateUtil.isNull(record.getShopName()) ? "" : record.getShopName()));
                    sheet.addCell(new Number(col++, row, record.getShopMoney()));
                    sheet.addCell(new Number(col++, row, record.getPostmanMoney()));
                    sheet.addCell(new Number(col++, row, record.getCityAdminMoney()));
                    sheet.addCell(new Number(col++, row, record.getPlatformMoney()));
                    sheet.addCell(new Number(col++, row, record.getTotalPrice()));
                    sheet.addCell(new Number(col++, row, record.getShopAwardMoney()));
                    sheet.addCell(new Number(col++, row, record.getCityAdminAwardMoney()));
                    sheet.addCell(new Number(col++, row, record.getPlatformAwardMoney()));
                    sheet.addCell(new Number(col++, row, record.getTotalCount()));
                    String tmpName = "日结";
                    if (record.getSettlementType() == 3) {
                        tmpName = "三日结";
                    }
                    if (record.getSettlementType() == 7) {
                        tmpName = "周结";
                    }
                    if (record.getSettlementType() == 30) {
                        tmpName = "月结";
                    }
                    sheet.addCell(new Label(col++, row, tmpName));
                    sheet.addCell(new Label(col++, row, record.getStatementState() == 0 ? "未计算" : "已结算"));
                    if (record.getSettlementType() == 30) {
                        sheet.addCell(new Label(col++, row, new SimpleDateFormat("yyyy-MM").format(new Date(record.getStatementTime()))));
                    } else {
                        sheet.addCell(new Label(col++, row, DateUtils.sdfDate.get().format(new Date(record.getStatementTime()))));
                    }
                    sheet.addCell(new Label(col++, row, ValidateUtil.isNull(record.getVerifyUser()) ? "" : record.getVerifyUser()));
                    if (ValidateUtil.isAbsLong(record.getVerifyTime())) {
                        sheet.addCell(new Label(col++, row, DateUtils.sdfDateTime.get().format(new Date(record.getVerifyTime()))));
                    } else {
                        sheet.addCell(new Label(col++, row, ""));
                    }
                    sheet.addCell(new Label(col++, row, record.getIsSysAutoStatistic() == 1 ? "是" : "否"));
                    sheet.addCell(new Label(col++, row, record.getCreateUser()));
                    row++;     // 行
                }
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }

    @RequestMapping(method = RequestMethod.POST, value = "/admin/shop/order/statement/statistics/verify")
    @ResponseBody
    public String shopOrderStatementStatisticVerify(@LoginUser UserInfo user, String ids) {
        if (!User.hasSuperAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("权限不足");
        }
        if (ValidateUtil.isNull(ids)) {
            return AjaxResult.errorResult("请选择需要审核的账单");
        }
        Set<Long> idSet = CommonUtils.splitToSet(ids, Long.class);
        if (idSet == null || idSet.isEmpty()) {
            return AjaxResult.errorResult("请选择需要审核的账单");
        }
        String sql = " select * from " + PojoTables.ORDER_STATEMENT_STATISTICS + " where delFlag = 0 and id in (" + StringUtils.join(idSet, ",") + ")";
        List<OrderStatementStatistics> list = null;
        try {
            list = (List<OrderStatementStatistics>) UserDaoSql.getInstance().list(OrderStatementStatistics.class, sql);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (!ValidateUtil.isAbsList(list)) {
            return AjaxResult.errorResult("未获取到审核的订单");
        }
        list = list.stream().filter(item -> item.getStatementState() == 0).collect(Collectors.toList());
        if (!ValidateUtil.isAbsList(list)) {
            return AjaxResult.successResult();
        }
        try {
            verifyOrderStatementStatistics(list, user.getUsername());
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        return AjaxResult.successResult();
    }

    /**
     * 审核账单并到账
     *
     * @param list
     * @param opUser
     * @throws Exception
     */
    private void verifyOrderStatementStatistics(List<OrderStatementStatistics> list, String opUser) throws Exception {
        if (!ValidateUtil.isAbsList(list)) {
            return;
        }
        for (OrderStatementStatistics statementStatistics : list) {
            if (ValidateUtil.isNull(statementStatistics)) {
                throw new Exception("结算数据异常--->[" + statementStatistics.getId() + "]");
            }
            Set<String> orderNoSet = CommonUtils.splitToSet(statementStatistics.getOrderNos(), String.class);
            for (String orderNo : orderNoSet) {
                ResultInfo resultInfo = UserWalletMan.saveOrUpdateUserWallet(null, UserType.SHOP_OWNER, null, UserWalletMan.STATEMENT_DAO_ZHANG, 0.0d, 0.0d, opUser, orderNo);
                if (resultInfo == null) {
                    throw new Exception("订单[" + orderNo + "] 到账失败,请刷新后单独重试");
                }
                if (!resultInfo.isSuccess()) {
                    throw new Exception("订单[" + orderNo + "] 到账异常" + resultInfo.getError());
                }
                System.out.println("订单[" + orderNo + "]到账成功");
            }
            StringBuilder sql = new StringBuilder();
            sql.append(" update ").append(PojoTables.ORDER_STATEMENT_STATISTICS).append(" set statementState = 1")
                    .append(" ,verifyUser ='").append(opUser).append("' ")
                    .append(", verifyTime = ").append(DateUtils.getCurrTime())
                    .append(", updateUser ='").append(opUser).append("' ")
                    .append(" where delFlag = 0 and id = ").append(statementStatistics.getId()).append(" and  statementState = 0");
            try {
                UserDaoSql.getInstance().executeBySql(sql.toString());
            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception("账单[" + statementStatistics.getId() + "],到账异常,请刷新后重试");
            }
        }
    }

}
